/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import model.User;
import model.UserGroup;

/**
 *
 * @author modesto
 */
public class UserDAO extends DAO {
    
    public UserDAO() throws ClassNotFoundException, SQLException {
        super();
    }
    
    public void add(User u) throws SQLException {
        String sql = "INSERT INTO user VALUES (NULL, ?, ?, MD5(?), ?, ?)";
        
        PreparedStatement stmt = this.getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        
        stmt.setString(1, u.getName());
        stmt.setString(2, u.getLogin());
        stmt.setString(3, u.getPassword());
        stmt.setString(4, u.getEmail());
        stmt.setInt(5, u.getUserGroup().getId());
        
        stmt.executeUpdate();
        
        ResultSet rs = stmt.getGeneratedKeys();
        
        rs.first();
        
        u.setId(rs.getInt(1));
        
    }

    
    public int remove (User user) throws SQLException {
        String sql = "DELETE FROM user WHERE id = ?";
        
        PreparedStatement stmt = this.getConnection().prepareStatement(sql);
        
        stmt.setInt(1, user.getId());
        
        return stmt.executeUpdate();        
        
    }
    
    
    public int update (User user) throws SQLException {
        String sql = "UPDATE user SET name = ?, "
                + "login = ?, "
                + "password = MD5(?), "
                + "email = ?, "
                + "group_id = ? "
                + "WHERE id = ?";
        
        PreparedStatement stmt = this.getConnection().prepareStatement(sql);
        
        stmt.setString(1, user.getName());
        stmt.setString(2, user.getLogin());
        stmt.setString(3, user.getPassword());
        stmt.setString(4, user.getEmail());
        stmt.setInt(5, user.getUserGroup().getId());
        stmt.setInt(6, user.getId());
        
        return stmt.executeUpdate();
        
    }
    
    
    public ArrayList<User> getAll() throws SQLException {
        ArrayList<User> userList = new ArrayList<>();
        String sql = "SELECT * FROM user u "
                + "INNER JOIN usergroup ug ON u.group_id = ug.id";
        
        PreparedStatement stmt = this.getConnection().prepareStatement(sql);
        
        ResultSet rs = stmt.executeQuery();
        
        while (rs.next()) {
            User u = new User();
            UserGroup ug = new UserGroup();
            ug.setId(rs.getInt("ug.id"));
            ug.setName(rs.getString("ug.name"));
            u.setId(rs.getInt("u.id"));
            u.setName(rs.getString("u.name"));
            u.setLogin(rs.getString("u.login"));
            u.setPassword(rs.getString("u.password"));
            u.setEmail(rs.getString("u.email"));
            u.setUserGroup(ug);
            userList.add(u);
        }
        
        return userList;
    }
    
    
    
    
    
}
